

/*多条查询语句查询*/
//根据班级名查询出所有同学信息


import utils.DBUtil;

import java.sql.*;
import java.text.MessageFormat;
import java.util.Scanner;

public class Demo08 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection= DBUtil.getConnection();
            //查询所有班级信息
            String sql="select id,name from class";//实际开发中不写不带where等查询条件的查询语句  sql语句写出来先去跑一下，确定正确了再往这上面粘贴
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            //打印出所有班级信息
            while(resultSet.next()){
                long id=resultSet.getLong(1);
                String name=resultSet.getString(2);
                System.out.println(MessageFormat.format("班级Id={0} \t 班级名={1}",id,name));
            }
            //接收用户请求
            Scanner scanner=new Scanner(System.in);
            System.out.println("请输入要查询的班级：");
            String inputName=scanner.next();

            String sql1="select s.id,s.name,s.sno,s.age,s.gender,s.enrool_date,s.class_id,c.id c_id,c.'name'"+
                    "c_name from student s,class c where s.class_id=c.id and c.name=?";
            //重新获取statement
            statement=connection.prepareStatement(sql1);
            //用真实数据替换占位符
            statement.setString(1,inputName);
            //执行sql，获取结果集
            resultSet=statement.executeQuery();
            while(resultSet.next()){
                long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                String sno = resultSet.getString("sno");
                int age = resultSet.getInt("age");
                boolean gender = resultSet.getBoolean("gender");
                Date enroll_date = resultSet.getDate("enroll_date");
                long class_id = resultSet.getLong("class_id");
                String className=resultSet.getString("c_name");
                //打印结果
                System.out.println(MessageFormat.format("学生编号={0}，姓名={1}，学号={2}，年龄={3}，" +
                        "性别={4}，入学时间={5}，班级编号={6}，班级名={7}", id, name, sno, age, gender, enroll_date, class_id,className));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.close(resultSet,statement,connection);
        }
    }
}
